package models

import (
	"context"
	"database/sql"
	"time"

	"github.com/jmoiron/sqlx"
	"gopkg.in/guregu/null.v4"
)

type BusinessModel struct {
	DB *sqlx.DB
}

// 评论
type CourseReview struct { 
	Total 	 int		 `json:"-" db:"total"`
	ID 		 int64		 `json:"id" db:"id"`
	CourseID int64		 `json:"course_id" db:"course_id"`
	UserID   int64		 `json:"user_id" db:"user_id"`
	UserName string		 `json:"user_name" db:"user_name"`
	Content  string		 `json:"content" db:"content"`
	Rating   int 		 `json:"rating" db:"rating"`
	ReviewAt time.Time 	 `json:"review_at" db:"review_at"`
	ReplyID  null.Int 	 `json:"reply_id" db:"reply_id"`
	Reply 	 null.String `json:"reply" db:"reply"`
	ReplyAt  null.Time 	 `json:"reply_at" db:"reply_at"`
}

// 获取课程评论
func (m *BusinessModel) GetCourseReviews(courseId int64, filter *PageFilter) ([]CourseReview, *PageMeta, error) {
	query := `
		SELECT
			COUNT(1) OVER() as total, r.id AS id, r.course_id AS course_id,
			r.user_id AS user_id, u.name AS user_name, r.review_text AS content,
			r.rating AS rating, r.updated_at AS review_at,
			rr.id AS reply_id, rr.reply_text AS reply, rr.updated_at AS reply_at
		FROM business.reviews r
		LEFT JOIN business.review_replies rr ON r.id = rr.review_id
		INNER JOIN appuser.users u ON r.user_id = u.id
		WHERE r.course_id = $1 AND r.review_text IS NOT NULL
		ORDER BY review_at DESC
		LIMIT $2 OFFSET $3`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	reviews := []CourseReview{}
	err := m.DB.SelectContext(ctx, &reviews, query, courseId, filter.limit(), filter.start())
	if err != nil {
		return nil, nil, err
	}

	totalRecords := 0
	if len(reviews) > 0 {
		totalRecords = reviews[0].Total
	}

	pageMeta := filter.calculateMetadata(totalRecords)
	return reviews, pageMeta, nil
}

// 获取课程评论
func (m *BusinessModel) GetCourseReview(courseId, userId int64) (*CourseReview, error) {
	query := `
		SELECT
			COUNT(1) OVER() as total, r.id AS id, r.course_id AS course_id,
			r.user_id AS user_id, u.name AS user_name, r.review_text AS content,
			r.rating AS rating, r.updated_at AS review_at,
			rr.id AS reply_id, rr.reply_text AS reply, rr.updated_at AS reply_at
		FROM business.reviews r
		LEFT JOIN business.review_replies rr ON r.id = rr.review_id
		INNER JOIN appuser.users u ON r.user_id = u.id
		WHERE r.course_id = $1 AND r.review_text IS NOT NULL AND r.user_id = $2`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	review := CourseReview{}
	err :=m.DB.GetContext(ctx, &review, query, courseId, userId)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &review, nil
}

// 创建课程评论
func (m *BusinessModel) WriteCourseReview(userID, courseID int64, rating int, content string) (*CourseReview, error) {
	query := `
		INSERT INTO business.reviews (user_id, course_id, rating, review_text)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (user_id, course_id)
		DO UPDATE SET rating = $3, review_text = $4, updated_at = NOW()
		RETURNING id, user_id, course_id, rating, review_text, updated_at`

	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	review := CourseReview{UserName: "",}
	err := m.DB.QueryRowxContext(ctx, query, userID, courseID, rating, content).Scan(
		&review.ID, &review.UserID,
		&review.CourseID, &review.Rating,
		&review.Content, &review.ReviewAt,
	)
	if err != nil {
		return nil, err
	}
	return &review, nil
}

// 创建课程评论回复
func (m *BusinessModel) ReplyCourseReview(reviewID int64, reply string) error {
	query := `
		INSERT INTO business.review_replies (review_id, reply_text)
		VALUES ($1, $2)
		ON CONFLICT (review_id)
		DO UPDATE SET reply_text = $2, updated_at = NOW()`

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()

	_, err := m.DB.ExecContext(ctx, query, reviewID, reply)
	return err
}

// 添加课程到收藏
func (m *BusinessModel) AddToFavoriteCourse(userId, courseId int64) (error) {
	query := `
		INSERT INTO business.favorite_courses (user_id, course_id)
		VALUES ($1, $2)`

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()
	
	args := []interface{}{userId, courseId}
	_, err := m.DB.ExecContext(ctx, query, args...)
	return err
}

// 移除课程收藏
func (m *BusinessModel) RemoveFromFavoriteCourse(userId, courseId int64) (error) {
	query := `
		DELETE FROM business.favorite_courses
		WHERE user_id = $1 AND course_id = $2`

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()
	
	args := []interface{}{userId, courseId}
	_, err := m.DB.ExecContext(ctx, query, args...)
	return err
}

// 添加课程到购物车
func (m *BusinessModel) AddCourseToShoppingCart(userId, courseId int64, price float32) (error) {
	query := `
		INSERT INTO business.shopping_carts (user_id, course_id, price)
		VALUES ($1, $2, $3)`

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()
	
	args := []interface{}{userId, courseId, price}
	_, err := m.DB.ExecContext(ctx, query, args...)
	return err
}

// 移除购物车课程
func (m *BusinessModel) RemoveCourseFromShoppingCart(uid int64, courseIds []int64) (float32, error) {
	query := `
		WITH deleted AS (
			DELETE FROM business.shopping_carts
			WHERE user_id = ? AND course_id IN (?)
			RETURNING price
		)
		SELECT SUM(price) FROM deleted`

	query, args, err := sqlx.In(query, uid, courseIds)
	if err != nil {
		return 0, err
	}
	query = m.DB.Rebind(query)

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()
	
	var totalPrice null.Float
	err = m.DB.GetContext(ctx, &totalPrice, query, args...)
	if err != nil {
		return 0, err
	}

	var total float32 = 0.0
	if totalPrice.Valid {
		total = float32(totalPrice.Float64)
	}

	return total, nil
}

// 购买课程
func (m *BusinessModel) PurchaseCourse(userId int64, courseIds []int64) (float32, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()

	// 创建事务
	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return 0, err
	}

	var totalPrice float32 = 0.0
	for _, courseId := range courseIds { 
		var price float32
		sql1 := `
			DELETE FROM business.shopping_carts
			WHERE user_id = $1 AND course_id = $2
			RETURNING price`
		err = tx.QueryRowxContext(ctx, sql1, userId, courseId).Scan(&price)
		if err != nil {
			if err == sql.ErrNoRows {
				sql2 := `SELECT price FROM course.courses WHERE id = $1`
				err = tx.QueryRowxContext(ctx, sql2, courseId).Scan(&price)
				if err != nil {
					tx.Rollback()
					return 0, err
				}
			} else {
				tx.Rollback()
				return 0, err
			}
		}
		totalPrice += price
		sql3 := `INSERT INTO business.orders (user_id, course_id, price)
			VALUES ($1, $2, $3)`
		_, err := tx.ExecContext(ctx, sql3, userId, courseId, price)
		if err != nil {
			tx.Rollback()
			return 0, err
		}
	}

	err = tx.Commit()
	if err != nil {
		return 0, err
	}
	return totalPrice, nil
}

// 获取用户的课程订单
type CourseOrder struct {
	CourseBrief
	OrderAt     time.Time `json:"order_at" db:"order_at"`
}

func (m *BusinessModel) GetCourseOrderInInArray(courseIds []int64, uid int64) ([]*CourseOrder, error) {
	query := `
		SELECT
			c.id as id, c.title as title, c.image as image,
			COUNT(v.id) AS video_count, SUM(v.duration) as duration, c.level as level,
			o.price as price, u.name as author, u.id as author_id,
			(
				SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
			) AS student_count,
			o.created_at as order_at
		FROM course.courses c
		LEFT JOIN course.table_contents tc ON c.id = tc.course_id
		LEFT JOIN course.videos v ON tc.id = v.parent_id
		INNER JOIN appuser.users u ON c.owner_id = u.id
		INNER JOIN business.orders o ON c.id = o.course_id
		WHERE c.id IN (?) AND o.user_id = ?
		GROUP BY c.id, u.id, u.name, o.price, o.created_at
		ORDER BY o.created_at DESC`
	
	query, args, err := sqlx.In(query, courseIds, []int64{uid})
	if err != nil {
		return nil, err
	}
	query = m.DB.Rebind(query)

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()

	rows, err := m.DB.QueryxContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	courseOrders := []*CourseOrder{}
	for rows.Next() {
		var courseOrder CourseOrder
		err := rows.StructScan(&courseOrder)
		if err != nil {
			return nil, err
		}
		courseOrders = append(courseOrders, &courseOrder)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}
	return courseOrders, nil
}

// 获取用户的所有课程订单
type PruchasingCourse struct {
	CourseOrder
	TotalPrice   float32 `json:"-" db:"total_price"`
	Total        int     `json:"-" db:"total"`
}

func (m *BusinessModel) GetPurchasingCourses(uid int64, filter *PageFilter) ([]*PruchasingCourse, *PageMeta, error) {
	query := `
		SELECT
			SUM(o.price) OVER() as total_price, COUNT(1) OVER() as total,
			c.id as id, c.title as title, c.image as image,
			COUNT(v.id) AS video_count, SUM(v.duration) as duration, c.level as level,
			o.price as price, u.name as author, u.id as author_id,
			(
				SELECT COUNT(user_id) FROM business.orders o WHERE o.course_id = c.id
			) AS student_count,
			o.created_at as order_at
		FROM course.courses c
		LEFT JOIN course.table_contents tc ON c.id = tc.course_id
		LEFT JOIN course.videos v ON tc.id = v.parent_id
		INNER JOIN appuser.users u ON c.owner_id = u.id
		INNER JOIN business.shopping_carts o ON c.id = o.course_id
		WHERE o.user_id = :uid
		GROUP BY c.id, u.id, u.name, o.price, o.created_at
		ORDER BY o.created_at DESC
		LIMIT :length OFFSET :start`

	args := map[string]interface{}{
		"uid": uid,
		"length": filter.limit(),
		"start": filter.start(),
	}

	ctx, cancel := context.WithTimeout(context.Background(), 3 * time.Second)
	defer cancel()

	rows, err := m.DB.NamedQueryContext(ctx, query, args)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	courses := []*PruchasingCourse{}
	for rows.Next() {
		var course PruchasingCourse
		err := rows.StructScan(&course)
		if err != nil {
			return nil, nil, err
		}
		courses = append(courses, &course)
	}

	if err = rows.Err(); err != nil {
		return nil, nil, err
	}

	totalRecords := 0
	if len(courses) > 0 {
		totalRecords = courses[0].Total
	}

	pageMeta := filter.calculateMetadata(totalRecords)

	return courses, pageMeta, nil
}